package dao;

import pojo.Student;
import utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class StudentDaoImpl implements StudentDao{

    //查询所有学生信息
    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<Student>();
        Student stu = new Student();
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            String sql = "SELECT * FROM student ";
            statement = con.prepareStatement(sql);
            rs = statement.executeQuery();
            while(rs.next()){
                stu.setStuId(rs.getInt(1));
                stu.setStuName(rs.getString(2));
                stu.setSex(rs.getString(3));
                stu.setBirthday(rs.getDate(4));
                stu.setStuTel(rs.getString(5));
                stu.setClassId(rs.getInt(6));
                list.add(stu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con,statement,rs);
        }
        return list;
    }

    //按照学生id查询学生信息
    //提前规定格式，避免sql注入
    @Override
    public Student findByStuId(Integer stuId) {
        Student stu = new Student();
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            String sql = "SELECT * FROM student WHERE stuId = ?";
            statement = con.prepareStatement(sql);
            statement.setInt(1,stuId);
            rs = statement.executeQuery();
            while(rs.next()){
                stu.setStuId(rs.getInt(1));
                stu.setStuName(rs.getString(2));
                stu.setSex(rs.getString(3));
                stu.setBirthday(rs.getDate(4));
                stu.setStuTel(rs.getString(5));
                stu.setClassId(rs.getInt(6));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con,statement,rs);
        }
        return stu;
    }

    @Override
    public Student findByStuName(String stuName) {
        Student stu = new Student();
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            String sql = "SELECT * FROM student WHERE stuName = ?";
            statement = con.prepareStatement(sql);
            statement.setString(1,stuName);
            rs = statement.executeQuery();
            while(rs.next()){
                stu.setStuId(rs.getInt(1));
                stu.setStuName(rs.getString(2));
                stu.setSex(rs.getString(3));
                stu.setBirthday(rs.getDate(4));
                stu.setStuTel(rs.getString(5));
                stu.setClassId(rs.getInt(6));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con,statement,rs);
        }
        return stu;
    }

    //按照班级id查询学生信息
    @Override
    public ArrayList<Student> findByClassId(Integer classId) {
        ArrayList<Student> list = new ArrayList<Student>();
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            String sql = "SELECT * FROM student WHERE classId = ?";
            statement = con.prepareStatement(sql);
            statement.setInt(1,classId);
            rs = statement.executeQuery();
            while(rs.next()){
                Student stu = new Student();
                stu.setStuId(rs.getInt(1));
                stu.setStuName(rs.getString(2));
                stu.setSex(rs.getString(3));
                stu.setBirthday(rs.getDate(4));
                stu.setStuTel(rs.getString(5));
                stu.setClassId(rs.getInt(6));
                list.add(stu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con,statement,rs);
        }
        return list;
    }

    @Override
    public ArrayList<Student> findByStuSex(String stuSex) {
        ArrayList<Student> list = new ArrayList<Student>();

        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            String sql = "SELECT * FROM student WHERE sex = ?";
            statement = con.prepareStatement(sql);
            statement.setString(1,stuSex);
            rs = statement.executeQuery();
            while(rs.next()){
                Student stu = new Student();
                stu.setStuId(rs.getInt(1));
                stu.setStuName(rs.getString(2));
                stu.setSex(rs.getString(3));
                stu.setBirthday(rs.getDate(4));
                stu.setStuTel(rs.getString(5));
                stu.setClassId(rs.getInt(6));
                list.add(stu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con,statement,rs);
        }
        return list;
    }

    //添加学生信息
    @Override
    public int insert(Student stu) {
        Connection con = null;
        PreparedStatement statement = null;
        int result = 0;
        try {
            con = JDBCUtils.getConnection();
            String sql = "INSERT INTO student VALUES (?,?,?,?,?,?) ";
            statement = con.prepareStatement(sql);
            Date date = stu.getBirthday();
            SimpleDateFormat stf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = stf.format(date);
            statement.setInt(1,stu.getStuId());
            statement.setString(2,stu.getStuName());
            statement.setString(3,stu.getSex());
            statement.setString(4,birthday);
            statement.setString(5,stu.getStuTel());
            statement.setInt(6,stu.getClassId());
            result = statement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con,statement);
        }
        return result;
    }

    //更新学生信息
    @Override
    public int update(Student stu) {
        Connection con = null;
        PreparedStatement statement = null;
        int result = 0;
        try {
            con = JDBCUtils.getConnection();
            String sql = "UPDATE student SET stuId = ?,stuName = ?,sex = ?,birthday = ? ,stuTel = ?, classId = ? WHERE stuId = ?";
            statement = con.prepareStatement(sql);
            Date date = stu.getBirthday();
            SimpleDateFormat stf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = stf.format(date);
            statement.setInt(1,stu.getStuId());
            statement.setString(2,stu.getStuName());
            statement.setString(3,stu.getSex());
            statement.setString(4,birthday);
            statement.setString(5,stu.getStuTel());
            statement.setInt(6,stu.getClassId());
            statement.setInt(7,stu.getStuId());
            result = statement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con,statement);
        }
        return result;
    }

    //删除信息
    @Override
    public int delete(Integer stuId) {
        Connection con = null;
        PreparedStatement statement = null;
        int result = 0;
        try {
            con = JDBCUtils.getConnection();
            String sql = "DELETE FROM student  WHERE stuId = ?  ";
            statement = con.prepareStatement(sql);
            statement.setInt(1,stuId);
            result = statement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con,statement);
        }
        return result;
    }
}
